﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;

namespace OALPTool
{
    public partial class FrmOLAPTool : Form
    {
        #region 自定义变量
        private int ret;
        private string strMsg;
        private DataView dvExcel = null;
        #endregion

        public FrmOLAPTool()
        {
            InitializeComponent();
        }

        #region 界面事件函数

        /// <summary>
        /// 工具栏
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void tb_Menu_ButtonClick(object sender, ToolBarButtonClickEventArgs e)
        {
            if (e.Button.Text == "退出")
            {
                Close();
            }
            else if (e.Button.Text == "导出")
            {
                //dgvTmplate.ExportExcel();
                ret = OutToExcel(out strMsg);
                if (ret < 0)
                {
                    TSysPublic.ShowMsg(strMsg);
                    return;
                }

            }
            else if (e.Button.Text == "导入")
            {

            }
        }

        /// <summary>
        /// 选择文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSelFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog file = new OpenFileDialog();
            file.Title = "请选择Excel文件";
            file.Filter = "Excel文件(*.xls)|*.xls";
            if (file.ShowDialog() == DialogResult.OK)
            {
                tbFilePath.Text = file.FileName;
                try
                {
                    ArrayList sheets = ExcelSheetName(file.FileName.Trim());
                    cmbSheet.Items.Clear();
                    for (int i = 0; i < sheets.Count; i++)
                    {
                        cmbSheet.Items.Add(sheets[i].ToString().Trim());
                    }
                    if (cmbSheet.Items.Count > 0)
                    {
                        cmbSheet.SelectedIndex = 0;
                    }
                }
                catch (Exception ex)
                {
                    TSysPublic.ShowErrMsg(ex.Message);
                }
            }
            else
            {
                return;
            }
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnInput_Click(object sender, EventArgs e)
        {
            if (dvExcel != null)
            {
                dvExcel = null;
                dgvTmplate.DataSource = null;
            }
            if (tbFilePath.Text == "" || cmbSheet.Text == "")
            {
                TSysPublic.ShowMsg("请输入需要导入excel的路径和sheet表名！");
                return;
            }
            //读取Excel数据
            string sExcelFile = tbFilePath.Text.Trim();
            string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection connection = new OleDbConnection(sConnectionString);
            string strLinkSql = "Select * from [" + cmbSheet.Text.Trim() + "$]";

            OleDbDataAdapter adp = new OleDbDataAdapter(strLinkSql, connection);

            DataSet ds = new DataSet();
            try
            {
                adp.Fill(ds, "Book1");
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("读取表底示数数据不成功：" + ex.Message);

                return;
            }
            if (ds == null || ds.Tables.Count < 0 || ds.Tables[0].Rows.Count < 0)
            {
                TSysPublic.ShowMsg("读取表底示数数据为空！");

                return;
            }

            dvExcel = new DataView(ds.Tables[0]);

            //初始化表头
            ret = InitHeader();

            if (ret < 0)
            {
                TSysPublic.ShowMsg("初始化表头失败！");
                return;
            }


            //设置Excel格式
            ret = FormatExcelData();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("设置Excel格式失败！");
                return;
            }

            // dgvTmplate.AutoGenerateColumns = false;
            dgvTmplate.DataSource = dvExcel;

            //设置DataGridView样式
            ret = SetDgvStyle();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("设置DataGridView样式失败！");
                return;
            }


        }

        /// <summary>
        /// 计算费率
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void butComputeExcelDate_Click(object sender, EventArgs e)
        {
            this.dgvTmplate.CellFormatting += new System.Windows.Forms.DataGridViewCellFormattingEventHandler(this.dgvTmplate_CellFormatting);

            ret = ComputeExcelDate();
            if (ret < 0)
            {
                TSysPublic.ShowMsg("计算费率失败！");
                return;
            }
        }

        /// <summary>
        /// 费用审核
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void butColorExcelData_Click(object sender, EventArgs e)
        {
            //ret = ColorExcelData();
            //if (ret < 0)
            //{
            //    TSysPublic.ShowMsg("费用审核失败！");
            //    return;
            //}
            Cursor.Current = Cursors.WaitCursor;
            ExportExcelColor ExportExcelColor = new ExportExcelColor();
            new ExportExcelColor().Export2Excel(dgvTmplate, dgvTmplate.ColumnTreeView[0], true);
            Cursor.Current = Cursors.Default;
        }

        /// <summary>
        ///  重绘行颜色
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dgvTmplate_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            /*------------------------------------------------
             审核标准：									
             （一级客户，代理商及直供企业）。									
             ① 生理   代理商 25%     直供 30%  以上									
             ② 婴儿   代理商 20%     直供 30%  以上 （均含 20%、25%、30%）									
             费率用红色、黄色标出。红色为高（高于以上标准或者有费用没有销售的），黄色为低（低于10%）。									

             费用明细审核标准:									
             KA企业:									
             陈列费、堆卖费、店头建设费单项费率：6%以上（含6%）									
             促销小姐：10%以上（含10%）									
             同期比：单项费率：高于同期2%				当月预算和同期实绩进行对比。					
             代理商：									
             同期比：单项费率：高于同期2%
             ------------------------------------------------*/

            string SellLevel = dgvTmplate.Rows[e.RowIndex].Cells["SellLevel"].Value.ToString();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
            string Rates = dgvTmplate.Rows[e.RowIndex].Cells["Rates"].Value.ToString();
            int ColorColumnIndex = dgvTmplate.Rows[e.RowIndex].Cells["Rates"].ColumnIndex;
            string CNFlag = (this.dgvTmplate.DataSource as DataView).Table.Rows[e.RowIndex]["CNFlag"].ToString();
            string AsItemName = dgvTmplate.Rows[e.RowIndex].Cells["AsItemName"].Value.ToString();
            string BaseRates = dgvTmplate.Rows[e.RowIndex].Cells["Rates201214"].Value.ToString();//同期实绩
            string BudgetRates = dgvTmplate.Rows[e.RowIndex].Cells["Rates201304"].Value.ToString();//当月预算
            int ColorBudgetColumnIndex = dgvTmplate.Rows[e.RowIndex].Cells["Rates201304"].ColumnIndex;

            if (!string.IsNullOrEmpty(Rates) && !string.IsNullOrEmpty(BaseRates) && !string.IsNullOrEmpty(BudgetRates))
            {
                decimal dRates = TMath.ConvertToDecimal(Rates.Replace("%", "")) / 100m;
                decimal dBaseRates = TMath.ConvertToDecimal(BaseRates.Replace("%", "")) / 100m;
                decimal dBudgetRates = TMath.ConvertToDecimal(BudgetRates.Replace("%", "")) / 100m;

                if (rbPhysiological.Checked)
                {
                    //生理 
                    if (SellLevel.Contains("直供企业"))
                    {
                        if (dRates >= 0.3m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                        }
                        else if (dRates < 0.1m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Yellow.ToArgb());
                        }

                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        if ((dRates >= 0.25m) && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                        }
                        else if (dRates < 0.1m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Yellow.ToArgb());
                        }

                    }

                }
                else if (rbBaby.Checked)
                {
                    //婴儿
                    if (SellLevel.Contains("直供企业"))
                    {
                        if (dRates >= 0.3m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                        }
                        else if (dRates < 0.1m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Yellow.ToArgb());
                        }
                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        if (dRates >= 0.2m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                        }
                        else if (dRates < 0.1m && CNFlag.Contains("1"))
                        {
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                            dgvTmplate.Rows[e.RowIndex].Cells[ColorColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Yellow.ToArgb());
                        }
                    }
                }

                if (rbKA.Checked)
                {
                    if (AsItemName.Contains("陈列费") && CNFlag.Contains("0") && dRates >= 0.06m)
                    {
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                    }
                    if (AsItemName.Contains("堆卖费") && CNFlag.Contains("0") && dRates >= 0.06m)
                    {
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                    }
                    if (AsItemName.Contains("店头建设费") && CNFlag.Contains("0") && dRates >= 0.06m)
                    {
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                    }
                    if (AsItemName.Contains("促销小姐") && CNFlag.Contains("0") && dRates >= 0.1m)
                    {
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                        dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                    }

                }
                else if (rbDL.Checked)
                {

                }

                if (CNFlag.Contains("0") && dBudgetRates - dBaseRates > 0.02m)
                {
                    dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.Font = new Font(DataGridView.DefaultFont, FontStyle.Bold);
                    dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.ForeColor = Color.FromArgb((Int32)Color.Black.ToArgb());
                    dgvTmplate.Rows[e.RowIndex].Cells[ColorBudgetColumnIndex].Style.BackColor = Color.FromArgb((Int32)Color.Red.ToArgb());
                }

            }
        }


        #endregion

        #region 辅助函数
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="DGV"></param>
        /// <param name="strErr"></param>
        /// <returns></returns>
        private int OutToExcel(out string strMsg)
        {
            /*---------------------------KA格式----------------------------------
           级别04	级别05	级别06	级别07	级别08	级别09	级别02	 级别03	 		        			 		
           区域	    城市	级别            客户名          费用科目 

           12上（同比）1-4月实绩
           上 纳品Val	上 实绩	上 纳品实绩费率

           2013-01
           纳品Val	实绩	纳品实绩费率

           2013-02
           纳品Val	实绩	纳品实绩费率

            2013-03
            单位确认纳品金额	预算金额	纳品计划费率

            2013-04
            单位确认纳品金额	预算金额	纳品计划费率

            年间推移1月实绩+2月实绩+3月预算+4月预算
            销售金额	费用金额	费率

            原因说明（对当月预算进行说明）	解决方法
            --------------------------------------------- */
            strMsg = "";

            //构建报表表头
            DataTable dt = new DataTable();
            dt.Columns.Add("BegRow", typeof(int));
            dt.Columns.Add("EndRow", typeof(int));
            dt.Columns.Add("BegCol", typeof(int));
            dt.Columns.Add("EndCol", typeof(int));
            dt.Columns.Add("Text", typeof(string));

            ArrayList list1 = new ArrayList();
            DataRow dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 1;
            dr["EndCol"] = 1;
            dr["Text"] = "级别 04";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 2;
            dr["EndCol"] = 2;
            dr["Text"] = "级别05";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 3;
            dr["EndCol"] = 3;
            dr["Text"] = "级别06";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 4;
            dr["EndCol"] = 4;
            dr["Text"] = "级别07";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 5;
            dr["EndCol"] = 5;
            dr["Text"] = "级别08";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 6;
            dr["EndCol"] = 6;
            dr["Text"] = "级别09";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 7;
            dr["EndCol"] = 7;
            dr["Text"] = "级别02";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 8;
            dr["EndCol"] = 8;
            dr["Text"] = "级别03";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 9;
            dr["EndCol"] = 11;
            dr["Text"] = "12上（同比）1-4月实绩";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 12;
            dr["EndCol"] = 14;
            dr["Text"] = "2013-01";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 15;
            dr["EndCol"] = 17;
            dr["Text"] = "2013-02";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 18;
            dr["EndCol"] = 20;
            dr["Text"] = "2013-03";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 21;
            dr["EndCol"] = 23;
            dr["Text"] = "2013-04";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 1;
            dr["BegCol"] = 24;
            dr["EndCol"] = 26;
            dr["Text"] = "年间推移1月实绩+2月实绩+3月预算+4月预算";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 2;
            dr["BegCol"] = 27;
            dr["EndCol"] = 27;
            dr["Text"] = "原因说明（对当月预算进行说明）";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 2;
            dr["BegCol"] = 28;
            dr["EndCol"] = 28;
            dr["Text"] = "解决方法";
            list1.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 1;
            dr["EndRow"] = 2;
            dr["BegCol"] = 29;
            dr["EndCol"] = 29;
            dr["Text"] = "计算公式";
            list1.Add(dr);

            ArrayList list2 = new ArrayList();
            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 1;
            dr["EndCol"] = 1;
            dr["Text"] = "区域";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 2;
            dr["EndCol"] = 2;
            dr["Text"] = "城市";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 3;
            dr["EndCol"] = 3;
            dr["Text"] = "级别";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 4;
            dr["EndCol"] = 4;
            dr["Text"] = "";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 5;
            dr["EndCol"] = 5;
            dr["Text"] = "客户名";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 6;
            dr["EndCol"] = 6;
            dr["Text"] = "";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 7;
            dr["EndCol"] = 7;
            dr["Text"] = "费用科目";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 8;
            dr["EndCol"] = 8;
            dr["Text"] = "";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 9;
            dr["EndCol"] = 9;
            dr["Text"] = "上 纳品Val";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 10;
            dr["EndCol"] = 10;
            dr["Text"] = "上 实绩";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 11;
            dr["EndCol"] = 11;
            dr["Text"] = "上 纳品实绩费率";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 12;
            dr["EndCol"] = 12;
            dr["Text"] = "纳品Val";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 13;
            dr["EndCol"] = 13;
            dr["Text"] = "实绩";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 14;
            dr["EndCol"] = 14;
            dr["Text"] = "纳品实绩费率";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 15;
            dr["EndCol"] = 15;
            dr["Text"] = "纳品Val";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 16;
            dr["EndCol"] = 16;
            dr["Text"] = "实绩";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 17;
            dr["EndCol"] = 17;
            dr["Text"] = "纳品实绩费率";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 18;
            dr["EndCol"] = 18;
            dr["Text"] = "单位确认纳品金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 19;
            dr["EndCol"] = 19;
            dr["Text"] = "预算金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 20;
            dr["EndCol"] = 20;
            dr["Text"] = "纳品计划费率";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 21;
            dr["EndCol"] = 21;
            dr["Text"] = "单位确认纳品金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 22;
            dr["EndCol"] = 22;
            dr["Text"] = "预算金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 23;
            dr["EndCol"] = 23;
            dr["Text"] = "纳品计划费率";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 24;
            dr["EndCol"] = 24;
            dr["Text"] = "销售金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 25;
            dr["EndCol"] = 25;
            dr["Text"] = "费用金额";
            list2.Add(dr);

            dr = dt.NewRow();
            dr["BegRow"] = 2;
            dr["EndRow"] = 2;
            dr["BegCol"] = 26;
            dr["EndCol"] = 26;
            dr["Text"] = "费率";
            list2.Add(dr);

            ArrayList list = new ArrayList();
            list.Add(list1);
            list.Add(list2);

            //Excel标题
            ArrayList titleList = new ArrayList();

            new TEntranceClass().SAL_DataGridViewHeadToExcel(dgvTmplate, list, titleList);

            //ArrayList Args = new ArrayList();
            //object objs = null;
            //int ret = 0;
            //Args.Add(dgvTmplate);
            //Args.Add(list);
            //Args.Add(titleList);
            //ret = TSysPublic.ExecFunction("SAL_DataGridViewHeadToExcel", ref Args, out objs);
            //if (ret < 0)
            //{
            //    TSysPublic.ShowMsg("导出到Excel失败!");
            //}

            return 0;
        }

        /// <summary>
        /// Excel连接串
        /// </summary>
        /// <param name="strFilePath"></param>
        /// <returns></returns>
        internal string Con2Excel(string strFilePath)
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=\"Excel 8.0;\"";
        }

        /// <summary>
        /// 获得Excel中的所有sheetname
        /// </summary>
        /// <param name="strFileName"></param>
        /// <returns></returns>
        internal ArrayList ExcelSheetName(string strFilePath)
        {
            ArrayList al = new ArrayList();
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(Con2Excel(strFilePath));
            conn.Open();
            DataTable sheetNames = conn.GetOleDbSchemaTable
                (System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            conn.Close();
            foreach (DataRow dr in sheetNames.Rows)
            {
                al.Add(dr[2].ToString().Replace("$", "").Replace("'", ""));
            }
            return al;
        }

        /// <summary>
        /// 初始化表头
        /// </summary>
        /// <returns></returns>
        public int InitHeader()
        {
            TreeNode treeNode1 = new TreeNode();
            treeNode1.Name = "节点16";
            treeNode1.Text = "区域";
            TreeNode treeNode2 = new TreeNode();
            treeNode2.Name = "节点0";
            treeNode2.Text = "级别 04";
            treeNode2.Nodes.Add(treeNode1);

            TreeNode treeNode3 = new TreeNode();
            treeNode3.Name = "节点17";
            treeNode3.Text = "城市";
            TreeNode treeNode4 = new TreeNode();
            treeNode4.Name = "节点1";
            treeNode4.Text = "级别 05";
            treeNode4.Nodes.Add(treeNode3);

            TreeNode treeNode5 = new TreeNode();
            treeNode5.Name = "节点18";
            treeNode5.Text = "级别";
            TreeNode treeNode6 = new TreeNode();
            treeNode6.Name = "节点2";
            treeNode6.Text = "级别 06";
            treeNode6.Nodes.Add(treeNode5);

            TreeNode treeNode7 = new TreeNode();
            treeNode7.Name = "节点19";
            treeNode7.Text = "";
            TreeNode treeNode8 = new TreeNode();
            treeNode8.Name = "节点3";
            treeNode8.Text = "级别 07";
            treeNode8.Nodes.Add(treeNode7);

            TreeNode treeNode9 = new TreeNode();
            treeNode9.Name = "节点20";
            treeNode9.Text = "客户名";
            TreeNode treeNode10 = new TreeNode();
            treeNode10.Name = "节点4";
            treeNode10.Text = "级别 08";
            treeNode10.Nodes.Add(treeNode9);

            TreeNode treeNode11 = new TreeNode();
            treeNode11.Name = "节点21";
            treeNode11.Text = "";
            TreeNode treeNode12 = new TreeNode();
            treeNode12.Name = "节点5";
            treeNode12.Text = "级别 09";
            treeNode12.Nodes.Add(treeNode11);

            TreeNode treeNode13 = new TreeNode();
            treeNode13.Name = "节点22";
            treeNode13.Text = "费用科目";
            TreeNode treeNode14 = new TreeNode();
            treeNode14.Name = "节点6";
            treeNode14.Text = "级别 02";
            treeNode14.Nodes.Add(treeNode13);

            TreeNode treeNode15 = new TreeNode();
            treeNode15.Name = "节点23";
            treeNode15.Text = "";
            TreeNode treeNode16 = new TreeNode();
            treeNode16.Name = "节点7";
            treeNode16.Text = "级别 03";
            treeNode16.Nodes.Add(treeNode15);

            TreeNode treeNode17 = new TreeNode();
            treeNode17.Name = "节点24";
            treeNode17.Text = "上 纳品Val";
            TreeNode treeNode18 = new TreeNode();
            treeNode18.Name = "节点25";
            treeNode18.Text = "上 实绩";
            TreeNode treeNode19 = new TreeNode();
            treeNode19.Name = "节点26";
            treeNode19.Text = "上 纳品实绩费率";
            TreeNode treeNode20 = new TreeNode();
            treeNode20.Name = "节点8";
            treeNode20.Text = "12上（同比）1-4月实绩";
            treeNode20.Nodes.Add(treeNode17);
            treeNode20.Nodes.Add(treeNode18);
            treeNode20.Nodes.Add(treeNode19);

            TreeNode treeNode21 = new TreeNode();
            treeNode21.Name = "节点27";
            treeNode21.Text = "纳品Val";
            TreeNode treeNode22 = new TreeNode();
            treeNode22.Name = "节点28";
            treeNode22.Text = "实绩";
            TreeNode treeNode23 = new TreeNode();
            treeNode23.Name = "节点29";
            treeNode23.Text = "纳品实绩费率";
            TreeNode treeNode24 = new TreeNode();
            treeNode24.Name = "节点9";
            treeNode24.Text = "2013-01";
            treeNode24.Nodes.Add(treeNode21);
            treeNode24.Nodes.Add(treeNode22);
            treeNode24.Nodes.Add(treeNode23);

            TreeNode treeNode25 = new TreeNode();
            treeNode25.Name = "节点30";
            treeNode25.Text = "纳品Val";
            TreeNode treeNode26 = new TreeNode();
            treeNode26.Name = "节点31";
            treeNode26.Text = "实绩";
            TreeNode treeNode27 = new TreeNode();
            treeNode27.Name = "节点32";
            treeNode27.Text = "纳品实绩费率";
            TreeNode treeNode28 = new TreeNode();
            treeNode28.Name = "节点10";
            treeNode28.Text = "2013-02";
            treeNode28.Nodes.Add(treeNode25);
            treeNode28.Nodes.Add(treeNode26);
            treeNode28.Nodes.Add(treeNode27);


            TreeNode treeNode29 = new TreeNode();
            treeNode29.Name = "节点33";
            treeNode29.Text = "单位确认纳品金额";
            TreeNode treeNode30 = new TreeNode();
            treeNode30.Name = "节点34";
            treeNode30.Text = "预算金额";
            TreeNode treeNode31 = new TreeNode();
            treeNode31.Name = "节点35";
            treeNode31.Text = "纳品计划费率";
            TreeNode treeNode32 = new TreeNode();
            treeNode32.Name = "节点11";
            treeNode32.Text = "2013-03";
            treeNode32.Nodes.Add(treeNode29);
            treeNode32.Nodes.Add(treeNode30);
            treeNode32.Nodes.Add(treeNode31);

            TreeNode treeNode33 = new TreeNode();
            treeNode33.Name = "节点36";
            treeNode33.Text = "单位确认纳品金额";
            TreeNode treeNode34 = new TreeNode();
            treeNode34.Name = "节点37";
            treeNode34.Text = "预算金额";
            TreeNode treeNode35 = new TreeNode();
            treeNode35.Name = "节点38";
            treeNode35.Text = "纳品计划费率";
            TreeNode treeNode36 = new TreeNode();
            treeNode36.Name = "节点12";
            treeNode36.Text = "2013-04";
            treeNode36.Nodes.Add(treeNode33);
            treeNode36.Nodes.Add(treeNode34);
            treeNode36.Nodes.Add(treeNode35);

            TreeNode treeNode37 = new TreeNode();
            treeNode37.Name = "节点39";
            treeNode37.Text = "销售金额";
            TreeNode treeNode38 = new TreeNode();
            treeNode38.Name = "节点40";
            treeNode38.Text = "费用金额";
            TreeNode treeNode39 = new TreeNode();
            treeNode39.Name = "节点41";
            treeNode39.Text = "费率";
            TreeNode treeNode40 = new TreeNode();
            treeNode40.Name = "节点13";
            treeNode40.Text = "年间推移1月实绩+2月实绩+3月预算+4月预算";
            treeNode40.Nodes.Add(treeNode37);
            treeNode40.Nodes.Add(treeNode38);
            treeNode40.Nodes.Add(treeNode39);

            TreeNode treeNode41 = new TreeNode();
            treeNode41.Name = "节点14";
            treeNode41.Text = "原因说明";

            TreeNode treeNode42 = new TreeNode();
            treeNode42.Name = "节点15";
            treeNode42.Text = "解决方法";

            //TreeNode treeNode43 = new TreeNode();
            //treeNode43.Name = "节点42";
            //treeNode43.Text = "序号";

            //TreeNode treeNode44 = new TreeNode();
            //treeNode44.Name = "节点43";
            //treeNode44.Text = "费率计算";

            TreeNode treeNode45 = new TreeNode();
            treeNode45.Name = "节点44";
            treeNode45.Text = "计算公式";

            //TreeNode treeNode46 = new TreeNode();
            //treeNode46.Name = "节点45";
            //treeNode46.Tag = "hide";
            //treeNode46.Text = "汇总标识";

            TreeView tree = new TreeView();
            tree.Nodes.Add(treeNode2);
            tree.Nodes.Add(treeNode4);
            tree.Nodes.Add(treeNode6);
            tree.Nodes.Add(treeNode8);
            tree.Nodes.Add(treeNode10);
            tree.Nodes.Add(treeNode12);
            tree.Nodes.Add(treeNode14);
            tree.Nodes.Add(treeNode16);
            tree.Nodes.Add(treeNode20);
            tree.Nodes.Add(treeNode24);
            tree.Nodes.Add(treeNode28);
            tree.Nodes.Add(treeNode32);
            tree.Nodes.Add(treeNode36);
            tree.Nodes.Add(treeNode40);
            tree.Nodes.Add(treeNode41);
            tree.Nodes.Add(treeNode42);
            //tree.Nodes.Add(treeNode43);
            //tree.Nodes.Add(treeNode44);
            tree.Nodes.Add(treeNode45);
            //tree.Nodes.Add(treeNode46);

            TreeView[] tvs = new TreeView[1];
            tvs[0] = tree;

            //表头级数
            dgvTmplate.ColumnDeep = 2;
            dgvTmplate.ColumnTreeView = tvs;

            return 0;
        }

        /// <summary>
        /// 设置读取格式
        /// 初始化DataGridView数据源
        /// </summary>
        /// <returns></returns>
        private int FormatExcelData()
        {
            try
            {
                ArrayList colList = new ArrayList();
                colList.Add("区域");
                colList.Add("城市");
                colList.Add("级别");
                colList.Add("销售类别");
                colList.Add("客户名称");
                colList.Add("店铺名称");
                colList.Add("费用科目");
                colList.Add("科目明细");
                colList.Add("201214纳品Val");
                colList.Add("201214实绩");
                colList.Add("201214纳品实绩费率");
                colList.Add("201301纳品Val");
                colList.Add("201301实绩");
                colList.Add("201301纳品实绩费率");
                colList.Add("201302纳品Val");
                colList.Add("201302实绩");
                colList.Add("201302纳品实绩费率");
                colList.Add("201303单位确认纳品金额");
                colList.Add("201303预算金额");
                colList.Add("201303纳品计划费率");
                colList.Add("201304单位确认纳品金额");
                colList.Add("201304预算金额");
                colList.Add("201304纳品计划费率");
                colList.Add("销售金额");
                colList.Add("费用金额");
                colList.Add("费率");
                colList.Add("原因说明");
                colList.Add("解决方法");

                foreach (DataColumn dcHeader in dvExcel.Table.Columns)
                {
                    if (!colList.Contains(dcHeader.ColumnName))
                    {
                        TSysPublic.ShowMsg("列 " + dcHeader.ColumnName + " 列头名称不合规范，请检查！");
                        return -1;
                    }
                }

                dvExcel.Table.Columns["区域"].ColumnName = "Zone"; // 级别04-区域 
                dvExcel.Table.Columns["城市"].ColumnName = "City";//级别05-城市
                dvExcel.Table.Columns["级别"].ColumnName = "SellLevel";//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                dvExcel.Table.Columns["销售类别"].ColumnName = "SellKind";//级别07-销售类别 (零售、网购)
                dvExcel.Table.Columns["客户名称"].ColumnName = "CustomerName";//级别08-客户名称
                dvExcel.Table.Columns["店铺名称"].ColumnName = "ShopName";//级别09
                dvExcel.Table.Columns["费用科目"].ColumnName = "AsName";//级别02-费用科目
                dvExcel.Table.Columns["科目明细"].ColumnName = "AsItemName";// 级别03

                dvExcel.Table.Columns["201214纳品Val"].ColumnName = "SellAmount201214";
                dvExcel.Table.Columns["201214实绩"].ColumnName = "ExpenseAmount201214";
                dvExcel.Table.Columns["201214纳品实绩费率"].ColumnName = "Rates201214";

                dvExcel.Table.Columns["201301纳品Val"].ColumnName = "SellAmount201301";
                dvExcel.Table.Columns["201301实绩"].ColumnName = "ExpenseAmount201301";
                dvExcel.Table.Columns["201301纳品实绩费率"].ColumnName = "Rates201301";

                dvExcel.Table.Columns["201302纳品Val"].ColumnName = "SellAmount201302";
                dvExcel.Table.Columns["201302实绩"].ColumnName = "ExpenseAmount201302";
                dvExcel.Table.Columns["201302纳品实绩费率"].ColumnName = "Rates201302";

                dvExcel.Table.Columns["201303单位确认纳品金额"].ColumnName = "SellAmount201303";
                dvExcel.Table.Columns["201303预算金额"].ColumnName = "ExpenseAmount201303";
                dvExcel.Table.Columns["201303纳品计划费率"].ColumnName = "Rates201303";

                dvExcel.Table.Columns["201304单位确认纳品金额"].ColumnName = "SellAmount201304";
                dvExcel.Table.Columns["201304预算金额"].ColumnName = "ExpenseAmount201304";
                dvExcel.Table.Columns["201304纳品计划费率"].ColumnName = "Rates201304";

                dvExcel.Table.Columns["销售金额"].ColumnName = "SellAmount";//=SUM(L21,O21,R21,U21)
                dvExcel.Table.Columns["费用金额"].ColumnName = "ExpenseAmount";//=SUM(M21,P21,S21,V21)
                dvExcel.Table.Columns["费率"].ColumnName = "Rates";//=费用金额/销售金额
                dvExcel.Table.Columns["原因说明"].ColumnName = "Brief";//对当月预算进行说明
                dvExcel.Table.Columns["解决方法"].ColumnName = "Solution";//


                if (!dvExcel.Table.Columns.Contains("ListNo"))
                {
                    dvExcel.Table.Columns.Add("ListNo", typeof(int));
                }

                if (!dvExcel.Table.Columns.Contains("Formula"))
                {
                    dvExcel.Table.Columns.Add("Formula", typeof(string));
                }
                if (!dvExcel.Table.Columns.Contains("CNFlag"))
                {
                    dvExcel.Table.Columns.Add("CNFlag", typeof(string));
                }
                int ListNo = 3;
                string SellLevel = "";//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                string SellKind = "";//级别07-销售类别 (零售、网购)
                string CustomerName = "";//级别08-客户名称
                string ShopName = "";////级别09-店铺名称***
                foreach (DataRowView drExcel in dvExcel)
                {
                    drExcel["ListNo"] = ListNo++;
                    SellLevel = drExcel["SellLevel"].ToString().Trim();
                    if (SellLevel.Contains("直供企业"))
                    {
                        string ShopNameTemp = drExcel["ShopName"].ToString().Trim();
                        if (!string.IsNullOrEmpty(ShopNameTemp))
                        {
                            if (!ShopNameTemp.Contains("汇总"))
                            {
                                CustomerName = drExcel["CustomerName"].ToString().Trim();
                                ShopName = ShopNameTemp;
                                drExcel["CNFlag"] = "0";
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                            else
                            {
                                drExcel["CustomerName"] = CustomerName;
                                ShopName = "";
                                drExcel["CNFlag"] = "1";
                                drExcel["SellAmount"] = TMath.ConvertToDecimal(drExcel["SellAmount201301"]) + TMath.ConvertToDecimal(drExcel["SellAmount201302"]) + TMath.ConvertToDecimal(drExcel["SellAmount201303"]) + TMath.ConvertToDecimal(drExcel["SellAmount201304"]);
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                        }
                        else
                        {
                            drExcel["CustomerName"] = CustomerName;
                            drExcel["ShopName"] = ShopName;
                            drExcel["CNFlag"] = "0";
                            drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                        }
                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        string SellKindTemp = drExcel["SellKind"].ToString().Trim();

                        if (!string.IsNullOrEmpty(SellKindTemp))
                        {
                            if (!SellKindTemp.Contains("汇总"))
                            {
                                SellKind = SellKindTemp;
                                drExcel["CNFlag"] = "0";
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                            else
                            {
                                SellKind = "";
                                drExcel["CNFlag"] = "1";
                                drExcel["SellAmount"] = TMath.ConvertToDecimal(drExcel["SellAmount201301"]) + TMath.ConvertToDecimal(drExcel["SellAmount201302"]) + TMath.ConvertToDecimal(drExcel["SellAmount201303"]) + TMath.ConvertToDecimal(drExcel["SellAmount201304"]);
                                drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                            }
                        }
                        else
                        {
                            drExcel["SellKind"] = SellKind;
                            drExcel["CNFlag"] = "0";
                            drExcel["ExpenseAmount"] = TMath.ConvertToDecimal(drExcel["ExpenseAmount201301"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201302"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201303"]) + TMath.ConvertToDecimal(drExcel["ExpenseAmount201304"]);
                        }
                    }
                }
                dvExcel.Sort = "ListNo";
                dvExcel.Table.AcceptChanges();
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("导入数据字段有问题：" + ex.Message);
                return -1;
            }
            return 0;
        }

        /// <summary>
        /// 设置DataGridView样式
        /// </summary>
        private int SetDgvStyle()
        {
            DataGridViewFunc.setDgvStyle(this.getColList(), ref this.dgvTmplate, this.dvExcel);

            this.dgvTmplate.AutoGenerateColumns = false;
            this.dgvTmplate.AllowUserToAddRows = false;
            this.dgvTmplate.AllowUserToDeleteRows = false;

            this.dgvTmplate.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            //不可排序
            foreach (DataGridViewColumn dgCol in this.dgvTmplate.Columns)
            {
                dgCol.SortMode = DataGridViewColumnSortMode.NotSortable;
            }
            return 0;
        }

        /// <summary>
        /// 获取DataGridView样式列表
        /// </summary>
        /// <returns>返回样式列表</returns>
        private ArrayList getColList()
        {
            ///   0:字段中文名；
            ///   1:字段英文名；
            ///   2:值类型【0-TextBox(默认),1-CheckBox,2-ComboBox,3-DateTimePicker,6-TextBox(自动换行),7-超链接】；
            ///   3:显示宽度；
            ///   4:显示格式化【"c"表示数字格式化；"d"表示时间格式化】；
            ///   5:对齐方式；
            ///   6:是否只读【true为只读；false为可修改】；
            ///   7:数据为空时显示；
            ///   8:是否存在转义；
            ///   9:显示顺序号；
            ///   10:颜色字段名【暂时未用】；
            ///   11:颜色字段意义列表【暂时未用】；
            ///   12:格式化字段；
            ///   13:暂时没用；
            ///   14:如果值类型为2，那么该值为Combol项集合对应数据集【每条数据对应一个object集合，object[0]为Display值，object[1]为Value值】  
            ///   
            ArrayList colList = new ArrayList();
            colList.Add(new object[] { "区域", "Zone", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "城市", "City", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "级别", "SellLevel", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "销售类别", "SellKind", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "客户名称", "CustomerName", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "店铺名称", "ShopName", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "费用科目", "AsName", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "科目明细", "AsItemName", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });

            colList.Add(new object[] { "201214纳品Val", "SellAmount201214", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201214实绩", "ExpenseAmount201214", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201214纳品实绩费率", "Rates201214", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });

            colList.Add(new object[] { "201301纳品Val", "SellAmount201301", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201301实绩", "ExpenseAmount201301", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201301纳品实绩费率", "Rates201301", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });

            colList.Add(new object[] { "201302纳品Val", "SellAmount201302", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201302实绩", "ExpenseAmount201302", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201302纳品实绩费率", "Rates201302", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });

            colList.Add(new object[] { "201303单位确认纳品金额", "SellAmount201303", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201303预算金额", "ExpenseAmount201303", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201303纳品计划费率", "Rates201303", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });

            colList.Add(new object[] { "201304单位确认纳品金额", "SellAmount201304", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201304预算金额", "ExpenseAmount201304", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "201304纳品计划费率", "Rates201304", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });

            colList.Add(new object[] { "销售金额", "SellAmount", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "费用金额", "ExpenseAmount", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "#,##0.00", "", "" });
            colList.Add(new object[] { "费率", "Rates", "0", 100, "c", DataGridViewContentAlignment.MiddleRight, false, "", null, 4, "", "", "0.00%", "", "" });
            colList.Add(new object[] { "原因说明", "Brief", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "解决方法", "Solution", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });
            colList.Add(new object[] { "计算公式", "Formula", "0", 180, null, DataGridViewContentAlignment.MiddleCenter, true, "", null, 1, "", "", "", "", "" });

            return colList;
        }

        /// <summary>
        /// 计算费率
        /// </summary>
        /// <returns></returns>
        private int ComputeExcelDate()
        {
            try
            {
                string strWhere = "CNFlag='1'";
                DataRow[] drrHZ = dvExcel.Table.Select(strWhere);
                foreach (DataRow drHZ in drrHZ)
                {
                    int ListNoHz = TMath.ConvertToInt(drHZ["ListNo"]);
                    decimal SellAmount = TMath.ConvertToDecimal(drHZ["SellAmount"]);
                    string SellLevelHZ = drHZ["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                    string NameHZ = "";
                    if (SellLevelHZ.Contains("直供企业"))
                    {
                        NameHZ = drHZ["ShopName"].ToString().Trim();
                    }
                    else if (SellLevelHZ.Contains("一级分销商"))
                    {
                        NameHZ = drHZ["SellKind"].ToString().Trim();
                    }
                    foreach (DataRowView drExcel in dvExcel)
                    {
                        string SellLevel = drExcel["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                        string Rates = drExcel["Rates"].ToString().Trim();
                        int ListNoExcel = TMath.ConvertToInt(drExcel["ListNo"]);
                        string CName = "";
                        if (SellLevel.Contains("直供企业"))
                        {
                            CName = drExcel["ShopName"].ToString().Trim();

                            if (!CName.Contains("汇总"))
                            {
                                CName = drExcel["ShopName"].ToString().Trim() + " 汇总";

                            }
                        }
                        else if (SellLevel.Contains("一级分销商"))
                        {
                            CName = drExcel["SellKind"].ToString().Trim();

                            if (!CName.Contains("汇总"))
                            {
                                CName = drExcel["SellKind"].ToString().Trim() + " 汇总";

                            }
                        }
                        if (NameHZ == CName && string.IsNullOrEmpty(Rates))
                        {
                            if (SellAmount > TMath.P_FINANCE_ZERO_2)
                            {
                                decimal ExpenseAmount = TMath.ConvertToDecimal(drExcel["ExpenseAmount"]);
                                //drExcel["Rates"] = (ExpenseAmount / SellAmount).ToString("P");
                                drExcel["Rates"] = ExpenseAmount / SellAmount;
                                drExcel["Formula"] = "=Y" + ListNoExcel + "/$X$" + ListNoHz;

                            }
                            else
                            {
                                drExcel["Rates"] = 0.00m;
                            }
                        }
                    }
                    dvExcel.Table.AcceptChanges();
                }
            }
            catch (Exception ex)
            {
                TSysPublic.ShowMsg("导入数据字段有问题：" + ex.Message);
                return -1;
            }

            return 0;
        }

        /// <summary>
        /// 设置染色
        /// </summary>
        /// <returns></returns>
        private int ColorExcelData()
        {

            //审核标准：									
            //（一级客户，代理商及直供企业）。									
            //① 生理   代理商 25%     直供 30%  以上									
            //② 婴儿   代理商 20%     直供 30%  以上 （均含 20%、25%、30%）									
            //费率用红色、黄色标出。红色为高（高于以上标准或者有费用没有销售的），黄色为低（低于10%）。									

            //费用明细审核标准:									
            //KA企业:									
            //陈列费、堆卖费、店头建设费单项费率：6%以上（含6%）									
            //促销小姐：10%以上（含10%）									
            //同期比：单项费率：高于同期2%				当月预算和同期实绩进行对比。					
            //代理商：									
            //同期比：单项费率：高于同期2%

            if (rbPhysiological.Checked)
            {
                //生理 
                foreach (DataRowView drExcel in dvExcel)
                {
                    string SellLevel = drExcel["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                    string Rates = drExcel["Rates"].ToString().Trim();

                    decimal dRates = TMath.ConvertToDecimal(Rates.Replace("%", "")) / 100m;


                    if (SellLevel.Contains("直供企业"))
                    {
                        if (dRates > 0.3m)
                        {

                        }

                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        if ((dRates > 0.25m))
                        {

                        }

                    }

                }

            }
            else if (rbBaby.Checked)
            {
                //婴儿
                foreach (DataRowView drExcel in dvExcel)
                {
                    string SellLevel = drExcel["SellLevel"].ToString().Trim();//级别06-级别 一级分销商(没有级别8 级别9) 直供企业（级别7为零售、网购 有级别8 级别9）
                    string Rates = drExcel["Rates"].ToString().Trim();
                    decimal dRates = TMath.ConvertToDecimal(Rates.Replace("%", "")) / 100m;
                    if (SellLevel.Contains("直供企业"))
                    {
                        if (dRates >= 0.3m)
                        {

                        }
                    }
                    else if (SellLevel.Contains("一级分销商"))
                    {
                        if (dRates >= 0.2m)
                        {

                        }
                    }
                }

            }
            return 0;
        }
        #endregion
    }
}
